package com.huayi.website.repository;

import com.huayi.website.entity.PhotoAttachmentEntity;
import com.huayi.website.entity.PhotoEntity;
import com.huayi.website.entity.ProductEntity;
import com.huayi.website.utils.PagingResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class PhotoRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Transactional
    public Integer editPhoto(PhotoEntity photo) {
        //如果存在，更新
        if (photo.getId() == null || photo.getId() == 0) {
            String goodsSQL = "INSERT INTO " + Tables.Photo + "(title,image,sort,`desc`,company_id) VALUE (?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(goodsSQL, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, photo.getTitle());
                    ps.setString(2, photo.getImages().get(0));
                    ps.setInt(3, photo.getSort());
                    ps.setString(4, photo.getDesc());
                    ps.setInt(5, photo.getCompanyId());

                    return ps;
                }
            }, keyHolder);

            Integer photoId = keyHolder.getKey().intValue();

            //添加附件
            String attSQL = " INSERT INTO " + Tables.PhotoAttachment + " (photo_id,attachment_type,url,create_on) VALUE (?,?,?,?)";

            //添加图片
            if (photo.getImages() != null && photo.getImages().size() > 0) {
                for (var img : photo.getImages()) {
                    if (StringUtils.isEmpty(img) == false)
                        jdbcTemplate.update(attSQL, photoId, 0, img, System.currentTimeMillis() / 1000);
                }
            }
            return photoId;
        } else {
            //更新
            String sql = "UPDATE " + Tables.Photo + "SET title=?,image=?,sort=?,`desc`=?,company_id=? WHERE id=?";
            jdbcTemplate.update(sql, photo.getTitle(), photo.getImages().get(0), photo.getSort(), photo.getDesc(),photo.getCompanyId(), photo.getId());

            //删除附件
            jdbcTemplate.update("DELETE FROM " + Tables.PhotoAttachment + " WHERE photo_id=?", photo.getId());
            //添加图片
            String attSQL = " INSERT INTO " + Tables.PhotoAttachment + " (photo_id,attachment_type,url,create_on) VALUE (?,?,?,?)";
            if (photo.getImages() != null && photo.getImages().size() > 0) {
                for (var img : photo.getImages()) {
                    if (StringUtils.isEmpty(img) == false)
                        jdbcTemplate.update(attSQL, photo.getId(), 0, img, System.currentTimeMillis() / 1000);
                }
            }

            return photo.getId();
        }
    }

    @Transactional
    public PagingResponse<PhotoEntity> getAllList(int pageIndex, int pageSize,Integer companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS p.* FROM").append(Tables.Photo).append(" as p ");
        sb.append(" where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(companyId!=null && companyId>0) {
            sb.append(" AND company_id=? ");
            params.add(companyId);
        }

        sb.append(" ORDER BY p.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(PhotoEntity.class), params.toArray(new Object[params.size()]));

        int total = jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
        if (list != null && list.size() > 0) {
            for (var photo : list) {
                var photoAtt = jdbcTemplate.query("select * from "+Tables.PhotoAttachment+" WHERE photo_id=?",new BeanPropertyRowMapper<>(PhotoAttachmentEntity.class),photo.getId());
                if(photoAtt!=null && photoAtt.size()>0){
                    for (var att:photoAtt) {
                        photo.getImages().add(att.getUrl());
                    }
                }
            }
        }
        return new PagingResponse<>(pageIndex, pageSize, total, list);
    }

    @Transactional
    public PhotoEntity getPhotoById(Integer photoId) {
        try {
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT SQL_CALC_FOUND_ROWS p.* FROM").append(Tables.Photo).append(" as p ");
            sb.append(" where id=? ");
            var photo = jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(PhotoEntity.class), photoId);

            var photoAtt = jdbcTemplate.query("select * from " + Tables.PhotoAttachment + " WHERE photo_id=?", new BeanPropertyRowMapper<>(PhotoAttachmentEntity.class), photo.getId());
            if (photoAtt != null && photoAtt.size() > 0) {
                for (var att : photoAtt) {
                    photo.getImages().add(att.getUrl());
                }
            }


            return photo;
        } catch (Exception e) {
            return null;
        }
    }

    public List<PhotoAttachmentEntity> getPhotoAttachmentListById(Integer photoId){
        return jdbcTemplate.query("select * from " + Tables.PhotoAttachment + " WHERE photo_id=?", new BeanPropertyRowMapper<>(PhotoAttachmentEntity.class), photoId);
    }

    @Transactional
    public void deletePhoto(Integer photoId) {
        //删除附件
        jdbcTemplate.update("DELETE FROM " + Tables.PhotoAttachment + " WHERE photo_id=?", photoId);
        //删除图集
        jdbcTemplate.update("DELETE FROM " + Tables.Photo + " WHERE id=?", photoId);
    }
}
